package cai.dao.impl;

import java.util.List;
import java.util.Map;

import search.dao.Impl.UniversalDaoImpl;
import cai.dao.ISsqDao;

public class SsqDao extends UniversalDaoImpl implements ISsqDao {
	int success = 90;

	public List getB(String dateid) {
		String sql = "select b,count(b) b_sum from ssq where dateid='" + dateid
				+ "' and success>=" + success + " group by b order by b";
		return queryForList(sql);
	}
	
	public List getR1(String dateid) {
				String sql = "select r1,count(r1) r1_sum from ssq where dateid='"
						+ dateid + "' and success>=" + success
						+ " group by r1 order by r1";
				return queryForList(sql);
	}
	public List getR2(String dateid) {
		String sql = "select r2,count(r2) r2_sum from ssq where dateid='"
				+ dateid + "' and success>=" + success
				+ " group by r2 order by r2";
		return queryForList(sql);
	}

	public List getR3(String dateid) {
		String sql = "select r3,count(r3) r3_sum from ssq where dateid='"
				+ dateid + "' and success>=" + success
				+ " group by r3 order by r3";
		return queryForList(sql);
	}

	public List getR4(String dateid) {
		String sql = "select r4,count(r4) r4_sum from ssq where dateid='"
				+ dateid + "' and success>=" + success
				+ " group by r4 order by r4";
		return queryForList(sql);
	}

	public List getR5(String dateid) {
		String sql = "select r5,count(r5) r5_sum from ssq where dateid='"
				+ dateid + "' and success>=" + success
				+ " group by r5 order by r5";
		return queryForList(sql);
	}

	public List getR6(String dateid) {
		String sql = "select r6,count(r6) r6_sum from ssq where dateid='"
				+ dateid + "' and success>=" + success
				+ " group by r6 order by r6";
		return queryForList(sql);
	}

	public List getBBypercent(String dateid) {
		String sql = "select b,100*cast(count(b) as float)/" + getCount(dateid)
				+ " b_sum from ssq where dateid='" + dateid + "' and success>="
				+ success + " group by b order by b_sum";
		return queryForList(sql);
	}

	public long getCount(String dateid) {
		String sql = "select count(*) s from ssq where dateid='" + dateid
				+ "' and success>=" + success + "";
		Map map = queryForMap(sql);
		return Long.parseLong(map.get("s") + "");
	}

	
	public List getR(String dateid) {
		String sql = "select r,sum(r_sum) r_sum from("
				+ "select r1 r,count(r1) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r1 "
				+ "union select r2 r,count(r2) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r2 "
				+ "union select r3 r,count(r3) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r3 "
				+ "union select r4 r,count(r4) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r4 "
				+ "union select r5 r,count(r5) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r5 "
				+ "union select r6 r,count(r6) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r6 ) t "
				+ "group by r order by r ";
		return queryForList(sql);
	}

	
	public List getRAvg(String dateid) {
		String sql = "select r,avg(r_sum) r_avg from("
				+ "select r1 r,count(r1) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r1 union select r2 r,count(r2) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r2 union select r3 r,count(r3) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r3 union select r4 r,count(r4) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r4 union select r5 r,count(r5) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r5 union select r6 r,count(r6) r_sum from ssq where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r6 ) t group by r order by r ";
		return queryForList(sql);
	}

}
